<?php

class DBmister extends DB
{

    public $divider = array("onPage" => 20);

    public function __construct($database = NULL, $server = NULL, $name = NULL, $password = NULL)
    {
        $this->Connect($database, $server, $name, $password);
    }
    
    /**
    * Ze stringu content_type ENUMu z DESCRIBE databáze vrátí seznam možných hodnot 
    * @param string $content_type_string Retezec vraceny z DESCRIBE ENUM sloupce
    * @return array Seznam moznych hodnot
    * @access private
    * @author Adam Motvicka
    */
    private function parseEnumOptions($content_type_string)
    {
        $fieldOptions = split("','",substr($content_type_string,1,-1));
        return $fieldOptions;
    }
    
    /**
    * Nacte popis sloupcu tabulky
    * @param string $table Jmeno tabulky
    * @return array Seznam sloupcu a jejich vlastnosti
    * @access public
    * @author Adam Motvicka
    */
    public function loadDescribe($table)
    {
        $describe = $this->retQuery("DESCRIBE `{$table}`");
        $key = $this->findKey($table);
        $settings = $this->loadTableSettings($table);
        $options = $this->loadOptionsSettings($table);
        $forkeys = $this->loadForeignKeys($table);
        
        foreach ($describe as $desc)
        {
            ereg('^([^ (]+)(\((.+)\))?([ ](.+))?$', $desc["Type"], $type);
            
            $row["field"] = $desc["Field"];
            $row["content_type"] = $type[1];
            $row["size"] = $type[3];
            if ($desc["Key"] == "PRI") $row["key"] = "primary";
            elseif ($desc["Key"] == "MUL") $row["key"] = "index";
            elseif ($desc["Key"] == "UNI") $row["key"] = "unique";
            else $row["key"] = false;
            $row["default"] = $desc["Default"];
            $row["extra"] = $desc["Extra"];
            if ($desc["Null"] == "YES") $row["null"] = true;
            else $row["null"] = false;
            if ($row["field"] == $key) $row["editkey"] = true;
            $row["set_title"] = $settings["columns"][$row["field"]]["title"];
            $row["set_note"] = $settings["columns"][$row["field"]]["note"];
            $row["forkey_string"] = $settings["columns"][$row["field"]]["forkey_string"];
            $row["visible"] = $settings["columns"][$row["field"]]["visible"];
            if (is_null($row["visible"])) $row["visible"] = true;
            $row["pattern"] = $settings["columns"][$row["field"]]["pattern"];

            if (array_key_exists($row["field"], $forkeys))
            {
                $row["forkey"] = $forkeys[$row["field"]];
            }
            
            // Nahrazeni pro konkretni typy se specifiky
            switch ($row["content_type"])
            {
                case "enum":
                    $row["options"] = $this->parseEnumOptions($row["size"]);
                    foreach ($row["options"] as $opt)
                    {
                        $row["options_set"][$opt] = $options[$row["field"]][$opt]["title"];
                        if (empty($row["options_set"][$opt])) $row["options_set"][$opt] = $opt;
                    }
                    break;
                    
                case "set":
                    $row["options"] = $this->parseEnumOptions($row["size"]);
                    foreach ($row["options"] as $opt)
                    {
                        $row["options_set"][$opt] = $options[$row["field"]][$opt]["title"];
                        if (empty($row["options_set"][$opt])) $row["options_set"][$opt] = $opt;
                    }
                    break;
            }
                         
            $return[] = $row;
            unset($row);
        }
        
        return $return;
    }
    
    /**
    * Nacte uzivatelske nastaveni sloupcu tabulky
    * @param string $table Jmeno tabulky
    * @return array Nastaveni
    * @access public
    * @author Adam Motvicka
    */
    public function loadTableSettings($table)
    {
        $columns = $this->retQuery("SELECT * FROM `dbmister`.`columns`
                                    WHERE `database` LIKE '{$GLOBALS["config"]["db"]["database"]}'
                                    AND `table` LIKE '{$table}'");
                                    
        foreach ($columns as $c) $return["columns"][$c["column"]] = $c;

        return $return;
    }
    
    /**
    * Nacte uzivatelske nastaveni hodnot vyberu
    * @param string $table Jmeno tabulky
    * @return array Nastaveni
    * @access public
    * @author Adam Motvicka
    */
    public function loadOptionsSettings($table)
    {
        $options = $this->retQuery("SELECT * FROM `dbmister`.`options`
                                    WHERE `database` LIKE '{$GLOBALS["config"]["db"]["database"]}'
                                    AND `table` LIKE '{$table}'");
        
        foreach ($options as $c) $return[$c["column"]][$c["option"]] = $c;

        return $return;
    }
    
    /**
    * Nacte uzivatelske nastaveni tabulek
    * @return array Nastaveni
    * @access public
    * @author Adam Motvicka
    */
    public function loadTablesSettings()
    {
        $tables = $this->retQuery("SELECT * FROM `dbmister`.`tables`
                                   WHERE `database` LIKE '{$GLOBALS["config"]["db"]["database"]}'");
                                    
        foreach ($tables as $t) $return[$t["table"]] = $t;

        return $return;
    }
    
    /**
    * Nacte data urciteho radku z tabulky 
    * @param string $table Nazev tabulky ze ktere bude cist
    * @param string $col_id Nazev sloupce ve kterem se hleda unikatni ID
    * @param string $val_id Hodnota ktera se bude hledat v urcitem sloupci
    * @return array Hodnoty radku
    * @access public
    * @author Adam Motvicka
    * @todo Osetrit vstupy
    */
    public function loadValues($table, $col_id, $val_id)
    {
        if (empty($col_id) OR empty($val_id)) return false;
        if ($col_id == "dbmister_where")
        {
        	$val_id = stripslashes($val_id);
            $return = $this->retQuery("SELECT * FROM `{$table}` WHERE {$val_id}");
        }
        else
        {
            $return = $this->retQuery("SELECT * FROM `{$table}` WHERE `{$col_id}` = '{$val_id}'");
        }
        return $return[0];
    }
    
    /**
    * Vycet radku tabulky
    * @param string $table Nazev tabulky ze ktere se ctou data
    * @param string $search Nastaveni vyhledavani
    * @return array Vycet radku
    * @access public
    * @author Adam Motvicka
    * @todo Osetrit vstupy
    */
    public function loadList($table, $search = NULL)
    {
        
        if (!is_null($search))
        {
            foreach ($search as $sk => $sv) if (empty($sv)) unset($search[$sk]);

            foreach ($search as $sk => $sv)
            {
                $where[] .= " `{$sk}` LIKE '{$sv}' ";
            }
            
            if (count($where) > 0)
            {
                $where_string = " WHERE " . implode(" AND ", $where);
            }
        }
        
        $this->divider["actualPage"] = $_GET["page"];
        if ($this->divider["actualPage"] == 0) $this->divider["actualPage"] = 1;
        $this->divider["limitFrom"] = ($this->divider["actualPage"] * $this->divider["onPage"]) - $this->divider["onPage"];
        
        $count = $this->retQuery("SELECT COUNT(*) AS count FROM `{$table}` {$where_string}");
        $this->divider["countLines"] = $count[0]["count"];
        $this->divider["countPages"] = ceil($this->divider["countLines"] / $this->divider["onPage"]);
        
        $return = $this->retQuery("SELECT * FROM `{$table}` {$where_string} LIMIT {$this->divider["limitFrom"]}, {$this->divider["onPage"]}");
        return $return;
    }
    
    /**
    * Vycet radku tabulky upravene pomoci patternu
    * @param string $table Nazev tabulky ze ktere se ctou data
    * @return array Vycet radku
    * @access public
    * @author Adam Motvicka
    * @todo Osetrit vstupy
    */
    public function loadListPattern($table, $key, $pattern)
    {
        $return = $this->retQuery("SELECT * FROM `{$table}`");
        
        foreach ($return as $ret)
        {
            foreach ($ret as $fbk => $fbv) $fs["[".$fbk."]"] = $fbv;
            $list[$ret[$key]] = strtr($pattern, $fs);
            unset($fs);
        }
        
        return $list;
    }
    
    /**
    * Seznam tabulek v databazi
    * @return array Vycet radku
    * @access public
    * @author Adam Motvicka
    * @todo Pridat moznost volitelneho parametru - nazev databaze kde se hledaji tabulky
    */
    public function loadListTables()
    {
        $tables = $this->retQuery("SHOW TABLES");
        $settings = $this->loadTablesSettings();
        
        foreach ($tables as $t) 
        {
            $k = array_values($t);
            $return[] = array("name" => $k[0],
                              "title" => $settings[$k[0]]["title"],
                              "visible" => $settings[$k[0]]["visible"],
                              "editable" => $settings[$k[0]]["editable"],
                              "type" => $settings[$k[0]]["type"],
                              "note" => $settings[$k[0]]["note"]);
            unset($k);
        }
        return $return;
    }
    
    /**
    * Najde sloupec v tabulce, ktery bude slouzit jako klic pro jednoznacnou identifikaci radku
    * @param string $table Nazev tabulky
    * @return string|bool Nazev identifikacniho sloupce, false pokud neexistuje
    * @access public
    * @author Adam Motvicka
    * @todo Osetrit vstupy
    * @todo Moznost identifikace v pripade nekolika PRI klicu v jedne tabulce? Jak vyresit?    
    */
    public function findKey($table)
    {
        $desc = $this->retQuery("DESCRIBE `{$table}`");
        
        $count = array
		(
			"primary"	=> 0,
			"unique"	=> 0
		);
		
        foreach ($desc as $d)
        {
        	if ($d['Key'] == "PRI") $count['primary']++;
        	if ($d['Key'] == "UNI")	$count['unique']++;
		}
		
		$key = false;
        foreach ($desc as $d)
        {
            if ($d["Extra"] == "auto_increment")
            {
                $key = $d["Field"];
                break;
            }
            
            if ($d['Key'] == "PRI" AND $count['primary'] == 1)
            {
				$key = $d["Field"];
                break;
			}
			
			if ($d['Key'] == "UNI" AND $count['unique'] == 1)
            {
				$key = $d["Field"];
                break;
			}
        }
        
        return $key;
    }
    
    /**
    * Najde cizi klice v tabulce
    * @param string $table Nazev tabulky
    * @return array Seznam sloupcu a tabulek na ktere ukazuje
    * @access public
    * @author Adam Motvicka
    * @todo Osetrit vstupy
    */
    function loadForeignKeys($table)
    {
        $result = $this->retQuery("SHOW CREATE TABLE `{$table}`");
        preg_match_all("|FOREIGN KEY \(`(.*)`\) REFERENCES `(.*)` \(`(.*)`\)|U", $result[0]["Create Table"], $out);
        
        foreach ($out[1] as $k => $v)
        {
             $return[$out[1][$k]] = array("remote_table" => $out[2][$k],
                                          "remote_field" => $out[3][$k],
                                          "local_field" => $out[1][$k]);
        }
        
        return $return;
    }
}

?>
